﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using CNCSoft.ErpApps.TMS.myclass;
using Common;
using CNCSoft.EData;
using System.Data.OleDb;

namespace CNCSoft.ErpApps.TMS.longGiaoVu
{
    public partial class frmDataContact : Form
    {
        private IFormatProvider culture = new System.Globalization.CultureInfo("fr-FR", true);
        private SqlConnection conn = new SqlConnection(GlobalVariable.WSInfo.ConnectionString);
        private DataSet ds = new DataSet();
        private CommonClass mine = new CommonClass();
        private string _strPath = "";

        public frmDataContact()
        {
            InitializeComponent();
        }

        private void frmDataContact_Load(object sender, EventArgs e)
        {
            GetData();
        }

        private void cmdUpdate_Click(object sender, EventArgs e)
        {
            UpdateData();
        }

        private void cmdCancel_Click(object sender, EventArgs e)
        {
            CancelData();
        }

        private void btnBrowser_Click(object sender, EventArgs e)
        {
            try
            {
                _strPath = mine.BrowseFileExcel("Excel Files(*.xls, *.xlsx) | *.xls");
                txtPath.Text = _strPath;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void btnInsertDataFromExcel_Click(object sender, EventArgs e)
        {
            try
            {
                InsertData();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        #region "Coding"
        private void GetAllContact()
        {
            try
            {
                if (ds.Tables["TableContact"] != null)
                    ds.Tables["TableContact"].Clear();
                string sql = @"SELECT * FROM Contact";
                SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                da.Fill(ds, "TableContact");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void GetData()
        {
            try
            {
                GetAllContact();
                gridControlDataContact.DataSource = ds;
                gridControlDataContact.DataMember = "TableContact";
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void UpdateData()
        {
            try
            {
                SqlDataAdapter daContact = new SqlDataAdapter(@"SELECT * FROM Contact", conn);
                DataHelper.buildDataCommands(ref daContact);
                daContact.Update(ds, "TableContact");
                GetData();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void CancelData()
        {
            try
            {
                ds.RejectChanges();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void InsertData()
        {
            try
            {
                string strConnExcel = mine.ConnectionStringToConnectExcel(_strPath);
                OleDbConnection oConn = new OleDbConnection(strConnExcel);
                oConn.Open();
                OleDbCommand oCmd = new OleDbCommand("SELECT * FROM [Sheet1$]", oConn);
                OleDbDataReader oDr = oCmd.ExecuteReader();
                string f_HoTen = "";
                string f_GioiTinh = "";
                string f_NgaySinh = "";
                string f_CMND = "";
                string f_DiaChi = "";
                string f_DienThoai = "";
                string f_Email = "";
                string f_HoTenPhuHuynh = "";
                string f_DienThoaiPhuHuynh = "";
                string f_NguonContact = "";
                while (oDr.Read())
                {
                    f_HoTen = mine.Valid(oDr, 0);
                    f_GioiTinh = mine.Valid(oDr, 1);
                    f_NgaySinh = mine.Valid(oDr, 2);
                    f_CMND = mine.Valid(oDr, 3);
                    f_DiaChi = mine.Valid(oDr, 4);
                    f_DienThoai = mine.Valid(oDr, 5);
                    f_Email = mine.Valid(oDr, 6);
                    f_HoTenPhuHuynh = mine.Valid(oDr, 7);
                    f_DienThoaiPhuHuynh = mine.Valid(oDr, 8);
                    f_NguonContact = mine.Valid(oDr, 9);

                    InsertDataExcelToSql(f_HoTen, f_GioiTinh, f_NgaySinh, f_CMND, 
                        f_DiaChi, f_DienThoai, f_Email, f_HoTenPhuHuynh, f_DienThoaiPhuHuynh, f_NguonContact);
                }
                oConn.Close();
                GetData();
                txtPath.Text = "";
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void InsertDataExcelToSql
            (string hoten, string gioitinh, string ngaysinh, 
            string cmnd, string diachi, string dienthoai, 
            string email, string hotenphuhuynh, string dienthoaiphuhuynh, string nguoncontact)
        {
            try
            {
                bool gt = false;
                int i = 0;
                SqlCommand cmdDataExcel = new SqlCommand();
                cmdDataExcel.Connection = conn;
                cmdDataExcel.CommandText =
                    @"INSERT INTO Contact(HoTen, GioiTinh, NgaySinh, SoCMND, 
                    DiaChi, DienThoai, Email, HoTenPhuHuynh, DienThoaiPhuHuynh, NguonContact, 
                    ContactDate) VALUES (@hoten, @gioitinh, @ngaysinh, @socmnd, @diachi, 
                    @dienthoai, @email, @hotenphuhuynh, @dienthoaiphuhuynh, @nguoncontact, @contactdate)";
                cmdDataExcel.Parameters.Add("@hoten", SqlDbType.NVarChar).Value = hoten;
                if(gioitinh.Trim().ToLower().Equals("nam"))
                    gt = true;
                else if(gioitinh.Trim().ToLower().Equals("nữ"))
                    gt = false;
                cmdDataExcel.Parameters.Add("@gioitinh", SqlDbType.Bit).Value = gt;
                cmdDataExcel.Parameters.Add("@ngaysinh", SqlDbType.DateTime).Value =
                    DateTime.Parse(ngaysinh, culture, System.Globalization.DateTimeStyles.AssumeLocal);
                cmdDataExcel.Parameters.Add("@socmnd", SqlDbType.VarChar).Value = cmnd;
                cmdDataExcel.Parameters.Add("@diachi", SqlDbType.NVarChar).Value = diachi;
                cmdDataExcel.Parameters.Add("@dienthoai", SqlDbType.VarChar).Value = dienthoai;
                cmdDataExcel.Parameters.Add("@email", SqlDbType.VarChar).Value = email;
                cmdDataExcel.Parameters.Add("@hotenphuhuynh", SqlDbType.NVarChar).Value = hotenphuhuynh;
                cmdDataExcel.Parameters.Add("@dienthoaiphuhuynh", SqlDbType.VarChar).Value = dienthoaiphuhuynh;
                cmdDataExcel.Parameters.Add("@nguoncontact", SqlDbType.NVarChar).Value = nguoncontact;
                cmdDataExcel.Parameters.Add("@contactdate", SqlDbType.DateTime).Value = DateTime.Now;
                cmdDataExcel.CommandType = CommandType.Text;
                try
                {
                    conn.Open();
                    i = cmdDataExcel.ExecuteNonQuery();
                }
                finally
                {
                    if (conn != null)
                        conn.Close();
                }
                /*if (i > 0)
                    MessageBox.Show("Data inserted successfully");
                 */
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        #endregion
    }
}
